package funtastic.spellingbee;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper
{
    public static final int     ERROR_CODE_NO_ERROR             = 0;
    public static final int     ERROR_CODE_NOT_FOUND            = 1002;
    public static final int     ERROR_CODE_NOT_UNIQUE           = 1001;

    private static final String TABLE_NAME_CUSTOM_SPELLING      = "CustomSpelling";
    private static final String TABLE_NAME_CUSTOM_SPELLING_LIST = "CustomSpellingList";

    private SQLiteDatabase      mDatabase;

    public DatabaseHelper(Context context, String databaseName, int databaseVersion)
    {
        super(context, databaseName, null, databaseVersion);

        mDatabase = getWritableDatabase();

        mDatabase.execSQL("PRAGMA foreign_keys=ON;");
    }

    public int addCustomSpelling(String customSpellingListName, String customSpellingName, String customSpellingSentence)
    {
        int errorCode = ERROR_CODE_NO_ERROR;

        String[] selectionArgs = new String[] { customSpellingListName };
        Cursor cursor = mDatabase.rawQuery("SELECT _id FROM " + TABLE_NAME_CUSTOM_SPELLING_LIST + " WHERE name=?", selectionArgs);

        if ((cursor.getCount() == 1) && cursor.moveToFirst())
        {
            mDatabase.execSQL("INSERT INTO " + TABLE_NAME_CUSTOM_SPELLING + " ('_idList', 'spelling', 'sentence') values ('" + cursor.getInt(0) + "', '" + customSpellingName + "', '" + customSpellingSentence + "')");
        }
        else
        {
            errorCode = ERROR_CODE_NOT_FOUND;
        }

        if ((cursor != null) && !cursor.isClosed())
        {
            cursor.close();
        }

        return errorCode;
    }

    public int addCustomSpellingList(String name)
    {
        int errorCode = ERROR_CODE_NO_ERROR;

        String[] selectionArgs = new String[] { name };
        Cursor cursor = mDatabase.rawQuery("SELECT name FROM " + TABLE_NAME_CUSTOM_SPELLING_LIST + " WHERE name=?", selectionArgs);

        if (cursor.getCount() == 0)
        {
            mDatabase.execSQL("INSERT INTO " + TABLE_NAME_CUSTOM_SPELLING_LIST + " ('name') values ('" + name + "')");
        }
        else
        {
            errorCode = ERROR_CODE_NOT_UNIQUE;
        }

        return errorCode;
    }

    public int deleteCustomSpelling(String customSpellingListName, String customSpellingName)
    {
        int errorCode = ERROR_CODE_NO_ERROR;

        String[] selectionArgs = new String[] { customSpellingListName };
        Cursor cursor = mDatabase.rawQuery("SELECT _id FROM " + TABLE_NAME_CUSTOM_SPELLING_LIST + " WHERE name=?", selectionArgs);

        if ((cursor.getCount() == 1) && cursor.moveToFirst())
        {
            mDatabase.execSQL("DELETE FROM " + TABLE_NAME_CUSTOM_SPELLING + " WHERE _idList = " + cursor.getInt(0) + " AND spelling = '" + customSpellingName + "'");
        }
        else
        {
            errorCode = ERROR_CODE_NOT_FOUND;
        }

        if ((cursor != null) && !cursor.isClosed())
        {
            cursor.close();
        }

        return errorCode;
    }

    public int deleteCustomSpellingList(String name)
    {
        int errorCode = ERROR_CODE_NO_ERROR;

        String[] whereArgs = new String[] { name };
        mDatabase.delete(TABLE_NAME_CUSTOM_SPELLING_LIST, "name=?", whereArgs);

        return errorCode;
    }

    public String[] getCustomSpellingLists()
    {
        Cursor cursor = mDatabase.query(TABLE_NAME_CUSTOM_SPELLING_LIST, new String[] { "name" }, null, null, null, null, "name");

        String[] customSpellingList = new String[cursor.getCount()];
        int index = 0;
        if (cursor.moveToFirst())
        {
            do
            {
                customSpellingList[index++] = cursor.getString(0);
            } while (cursor.moveToNext());
        }

        if ((cursor != null) && !cursor.isClosed())
        {
            cursor.close();
        }

        return customSpellingList;
    }

    public ArrayList<SpellingDetails> getCustomSpellings(String name)
    {
        ArrayList<SpellingDetails> customSpellings;

        String[] selectionArgs = new String[] { name };
        Cursor cursor = mDatabase.rawQuery("SELECT " + TABLE_NAME_CUSTOM_SPELLING + ".spelling, " + TABLE_NAME_CUSTOM_SPELLING + ".sentence FROM " + TABLE_NAME_CUSTOM_SPELLING + ", " + TABLE_NAME_CUSTOM_SPELLING_LIST + " WHERE "
                + TABLE_NAME_CUSTOM_SPELLING + "._idList = " + TABLE_NAME_CUSTOM_SPELLING_LIST + "._id AND " + TABLE_NAME_CUSTOM_SPELLING_LIST + ".name=?", selectionArgs);

        customSpellings = new ArrayList<SpellingDetails>(cursor.getCount());
        if (cursor.moveToFirst())
        {
            do
            {
                customSpellings.add(new SpellingDetails(cursor.getString(0), cursor.getString(1)));
            } while (cursor.moveToNext());
        }

        if ((cursor != null) && !cursor.isClosed())
        {
            cursor.close();
        }

        return customSpellings;
    }

    public String getCustomSpellingSentence(String customSpellingListName, String customSpellingName)
    {
        String sentence = "";

        String[] selectionArgsList = new String[] { customSpellingListName };
        Cursor cursorList = mDatabase.rawQuery("SELECT _id FROM " + TABLE_NAME_CUSTOM_SPELLING_LIST + " WHERE name=?", selectionArgsList);

        if ((cursorList.getCount() == 1) && cursorList.moveToFirst())
        {
            String[] selectionArgsSpelling = new String[] { customSpellingName };
            Cursor cursorSpelling = mDatabase.rawQuery(
                    "SELECT " + TABLE_NAME_CUSTOM_SPELLING + ".sentence FROM " + TABLE_NAME_CUSTOM_SPELLING + " WHERE " + TABLE_NAME_CUSTOM_SPELLING + "._idList = " + Integer.toString(cursorList.getInt(0)) + " AND "
                            + TABLE_NAME_CUSTOM_SPELLING + ".spelling=?", selectionArgsSpelling);

            if ((cursorSpelling.getCount() == 1) && cursorSpelling.moveToFirst())
            {
                sentence = cursorSpelling.getString(0);
            }

            if ((cursorSpelling != null) && !cursorSpelling.isClosed())
            {
                cursorSpelling.close();
            }
        }

        if ((cursorList != null) && !cursorList.isClosed())
        {
            cursorList.close();
        }

        return sentence;
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        db.execSQL("CREATE TABLE " + TABLE_NAME_CUSTOM_SPELLING_LIST + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)");
        db.execSQL("CREATE TABLE " + TABLE_NAME_CUSTOM_SPELLING + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, _idList INTEGER REFERENCES " + TABLE_NAME_CUSTOM_SPELLING_LIST
                + "(_id) ON DELETE CASCADE, spelling TEXT NOT NULL, sentence TEXT DEFAULT ' ')");
    }

    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2)
    {
    }

    public void print()
    {
        Log.i("funtastic.spellingbee", "START: Custom Database");
        
        Cursor cursor;
        String[] args = new String[0];

        cursor = mDatabase.rawQuery("SELECT * FROM " + TABLE_NAME_CUSTOM_SPELLING_LIST, args);
        Log.i("Database", TABLE_NAME_CUSTOM_SPELLING_LIST + " = " + cursor.getCount());
        if (cursor.moveToFirst())
        {
            do
            {
                Log.i("funtastic.spellingbee", TABLE_NAME_CUSTOM_SPELLING_LIST + ": " + cursor.getString(0) + " = " + cursor.getString(1));
            } while (cursor.moveToNext());
        }

        if ((cursor != null) && !cursor.isClosed())
        {
            cursor.close();
        }

        cursor = mDatabase.rawQuery("SELECT * FROM " + TABLE_NAME_CUSTOM_SPELLING, args);
        Log.i("Database", TABLE_NAME_CUSTOM_SPELLING + " = " + cursor.getCount());
        if (cursor.moveToFirst())
        {
            do
            {
                Log.i("funtastic.spellingbee", TABLE_NAME_CUSTOM_SPELLING + ": " + cursor.getString(0) + "::" + cursor.getString(1) + " = " + cursor.getString(2) + " : " + cursor.getString(3));
            } while (cursor.moveToNext());
        }

        if ((cursor != null) && !cursor.isClosed())
        {
            cursor.close();
        }
        
        Log.i("funtastic.spellingbee", "END: Custom Database");
    }
    
    public int updateCustomSpelling(String customSpellingList, String customSpellingOld, String customSpellingNew, String customSpellingSentence)
    {
        int errorCode = ERROR_CODE_NO_ERROR;

        String[] selectionArgs = new String[] { customSpellingList };
        Cursor cursorCustomSpellingList = mDatabase.rawQuery("SELECT _id FROM " + TABLE_NAME_CUSTOM_SPELLING_LIST + " WHERE name=?", selectionArgs);

        if ((cursorCustomSpellingList.getCount() == 1) && cursorCustomSpellingList.moveToFirst())
        {
            String[] argsSpelling = new String[] { customSpellingOld };
            Cursor cursorCustomSpelling = mDatabase.rawQuery("SELECT _id FROM " + TABLE_NAME_CUSTOM_SPELLING + " WHERE _idList=" + cursorCustomSpellingList.getInt(0) + " AND spelling=?", argsSpelling);

            if ((cursorCustomSpelling.getCount() == 1) && cursorCustomSpelling.moveToFirst())
            {
                Log.i("funtastic.spellingbee", "UPDATE " + TABLE_NAME_CUSTOM_SPELLING + " SET spelling = '" + customSpellingNew + "' AND sentence = '" + customSpellingSentence + "' WHERE _id = " + cursorCustomSpelling.getInt(0));
                mDatabase.execSQL("UPDATE " + TABLE_NAME_CUSTOM_SPELLING + " SET spelling = '" + customSpellingNew + "', sentence = '" + customSpellingSentence + "' WHERE " + TABLE_NAME_CUSTOM_SPELLING + "._id = " + cursorCustomSpelling.getInt(0));
            }
            else
            {
                errorCode = ERROR_CODE_NOT_FOUND;
            }

            if ((cursorCustomSpelling != null) && !cursorCustomSpelling.isClosed())
            {
                cursorCustomSpelling.close();
            }
        }
        else
        {
            errorCode = ERROR_CODE_NOT_FOUND;
        }

        if ((cursorCustomSpellingList != null) && !cursorCustomSpellingList.isClosed())
        {
            cursorCustomSpellingList.close();
        }

        return errorCode;
    }

    public int updateCustomSpellingList(String customSpellingListNameOld, String customSpellingListNameNew)
    {
        int errorCode = ERROR_CODE_NO_ERROR;

        String[] selectionArgs = new String[] { customSpellingListNameNew };
        Cursor cursor = mDatabase.rawQuery("SELECT name FROM " + TABLE_NAME_CUSTOM_SPELLING_LIST + " WHERE name=?", selectionArgs);

        if (cursor.getCount() == 0)
        {
            mDatabase.execSQL("UPDATE " + TABLE_NAME_CUSTOM_SPELLING_LIST + " SET name = '" + customSpellingListNameNew + "' WHERE name = '" + customSpellingListNameOld + "'");
        }
        else
        {
            errorCode = ERROR_CODE_NOT_UNIQUE;
        }

        return errorCode;
    }

    public int updateCustomSpellingSentence(String customSpellingList, String customSpelling, String customSpellingSentence)
    {
        int errorCode = ERROR_CODE_NO_ERROR;

        String[] selectionArgs = new String[] { customSpellingList };
        Cursor cursorCustomSpellingList = mDatabase.rawQuery("SELECT _id FROM " + TABLE_NAME_CUSTOM_SPELLING_LIST + " WHERE name=?", selectionArgs);

        if ((cursorCustomSpellingList.getCount() == 1) && cursorCustomSpellingList.moveToFirst())
        {
            String[] argsSpelling = new String[] { customSpelling };
            Cursor cursorCustomSpelling = mDatabase.rawQuery("SELECT _id FROM " + TABLE_NAME_CUSTOM_SPELLING + " WHERE _idList=" + cursorCustomSpellingList.getInt(0) + " AND spelling=?", argsSpelling);

            if ((cursorCustomSpelling.getCount() == 1) && cursorCustomSpelling.moveToFirst())
            {
                mDatabase.execSQL("UPDATE " + TABLE_NAME_CUSTOM_SPELLING + " SET sentence = '" + customSpellingSentence + "' WHERE _id = " + cursorCustomSpelling.getInt(0));
            }
            else
            {
                errorCode = ERROR_CODE_NOT_FOUND;
            }

            if ((cursorCustomSpelling != null) && !cursorCustomSpelling.isClosed())
            {
                cursorCustomSpelling.close();
            }
        }
        else
        {
            errorCode = ERROR_CODE_NOT_FOUND;
        }

        if ((cursorCustomSpellingList != null) && !cursorCustomSpellingList.isClosed())
        {
            cursorCustomSpellingList.close();
        }

        return errorCode;
    }

}
